CREATE TRANSFORM

CREATE TRANSFORM — Define a new transform

Synopsis

CREATE [ OR REPLACE ] TRANSFORM FOR type_name LANGUAGE lang_name ( FROM SQL WITH FUNCTION from_sql_function_name [ (argument_type [, ...]) ],

TO SQL WITH FUNCTION to_sql_function_name [ (argument_type [, ...]) ]

);

Introduction

CREATE TRANSFORM defines a new transform. CREATE OR REPLACE TRANSFORM will either create a new transform or replace an existing definition.

A transform specifies how to adapt a data type to a procedural language. For example, when writing a function in PL/Python that uses the hstore type, PL/Python has no prior knowledge of how to represent hstore values in the Python environment. Language implementations typically default to using a text representation, but this can be inconvenient in some cases, for example when an associative array or list might be more appropriate.

A transform specifies two functions:

• A "from SQL" function is responsible for converting the type from the SQL environment to the language. This function will be called on the arguments of a function written in the language.

• A "to SQL" function is responsible for converting the type from the language to the SQL environment. This function will be called on the return value of a function written in the language.

It is not necessary to provide both functions. If one is not specified, the language-specific default behavior will be used when necessary (to completely prevent a conversion in one direction, you can also write a transform function that always raises an error).

To create a transform, you must own the type and have the USAGE privilege on it, have the USAGE privilege on the language, and have the EXECUTE privilege on the from-SQL and to-SQL functions (if specified).

Parameters

type_name

The name of the data type of the transform.

lang_name

The name of the language of the transform.

from_sql_function_name[(argument_type [, ...])]

The name of the function to convert the type from the SQL environment to the language. It must accept one argument of type internal and return type internal. The actual argument will be of the type that the transform applies to, and the function should be written as if it were of that type (but it is not allowed to declare a SQL-level function returning internal without at least one argument of type internal). The actual return value will depend on the language implementation. If no argument list is specified, the function name must be unique in its schema.

to_sql_function_name[(argument_type [, ...])]

The name of the function to convert the type from the language to the SQL environment. It must accept one argument of type internal and return the type that the transform applies to. The actual argument value will depend on the language implementation. If no argument list is specified, the function name must be unique in its schema.

Notes

Use DROP TRANSFORM to remove a transform.

Examples

# To create a transform for type hstore and language plpythonu, first set up the type and language:

CREATE TYPE hstore ...;

CREATE EXTENSION plpythonu;

# Then create the required functions:

CREATE FUNCTION hstore_to_plpython(val internal) RETURNS internal

LANGUAGE C STRICT IMMUTABLE

AS ...;

CREATE FUNCTION plpython_to_hstore(val internal) RETURNS hstore

LANGUAGE C STRICT IMMUTABLE

AS ...;

# Finally create the transform to connect them:

CREATE TRANSFORM FOR hstore LANGUAGE plpythonu (

FROM SQL WITH FUNCTION hstore_to_plpython(internal),

TO SQL WITH FUNCTION plpython_to_hstore(internal)

);

# In practice, these commands would be wrapped in an extension.

# The contrib section contains some extensions that provide transforms, which can serve as practical examples.

See Also

CREATE FUNCTION, CREATE LANGUAGE, CREATE TYPE, DROP TRANSFORM